package bdhub.littletail.lib.database;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import bdhub.littletail.lib.utils.log.CLog;
import se.emilsjolander.sprinkles.Migration;

/**
 * 数据库初始化配置<br />
 * 
 * 如果想要自动建表，可以参考：https://github.com/elihart/picdora-android/blob/master/
 * library_projects
 * /sprinkles-master/src/se/emilsjolander/sprinkles/Migration.java <br />
 * 如果想灵活、精准一些，那就自己用 SQL 建表吧。
 * 
 * @author FreddyChen 2016-2-23
 */
public class DatabaseMigration extends Migration {

	private static final int VERSION_ON_V_1_0 = 1;

	public static final int VERSION = VERSION_ON_V_1_0;

	private static final String TABLE_USERS = "Users";
	private static final String TABLE_USERS_TEMP = "Users_temp";

	@Override
	protected void onPreMigrate() {

	}

	@Override
	protected void doMigration(SQLiteDatabase db, int oldVersion, int newVersion) {
		//1.0 版本的数据库。
		if (oldVersion < VERSION_ON_V_1_0) {
			CLog.show("init first data base tables.");

			// 如果存在旧版本的表，则执行数据迁移操作。
			// 1.先把旧表命名为临时表TABLE_temp
			// 2.创建新表TABLE，字段任意增减。注意：增减字段时后续的迁移动作字段数需要对应上，新字段的值为默认值即可
			// 3.数据迁移，从临时表TABLE_temp查询旧数据插入新表，字段需要对应上。
			// 4.删除临时表
			if (oldVersion > 0) {
				alterTableName(db, TABLE_USERS);
			}

			// 用户
			//			db.execSQL("DROP TABLE IF EXISTS Users");
			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_USERS + "("
					+ "id INTEGER PRIMARY KEY,"    // id
					+ "nickName TEXT,"    //昵称
					+ "isFriend INTEGER,"    //是否为好友
					+ "friendType INTEGER,"  //好友类型
					+ "relation INTEGER,"    //关系
					+ "pinyin TEXT,"        //名字的拼音
					+ "phoneNumber TEXT,"    //电话号码
					+ "avatar TEXT,"    //头像
					+ "gender TEXT,"    //性别
					+ "birthday INTEGER," //生日
					+ "province TEXT,"    //所在省
					+ "city TEXT,"        //所在市
					+ "sign TEXT,"    //签名
					+ "wechat TEXT," //微信
					+ "qq TEXT,"    //QQ
					+ "weibo TEXT,"    //微博
					+ "authData TEXT," //授权信息
					+ "album TEXT,"    //相册
					+ "backgroundImage TEXT,"// 个人背景
					+ "reserve TEXT" //保留字段
					+ ")");

			if (checkTableExists(db, TABLE_USERS_TEMP)) {
				// 数据迁移
				StringBuilder sql = new StringBuilder("INSERT INTO " + TABLE_USERS);
				sql.append(" SELECT ");
				insertColumn(db, sql, TABLE_USERS_TEMP, "id");
				insertColumn(db, sql, TABLE_USERS_TEMP, "nickName");
				insertColumn(db, sql, TABLE_USERS_TEMP, "isFriend");
				insertColumn(db, sql, TABLE_USERS_TEMP, "friendType");
				insertColumn(db, sql, TABLE_USERS_TEMP, "relation");
				insertColumn(db, sql, TABLE_USERS_TEMP, "pinyin");
				insertColumn(db, sql, TABLE_USERS_TEMP, "phoneNumber");
				insertColumn(db, sql, TABLE_USERS_TEMP, "avatar");
				insertColumn(db, sql, TABLE_USERS_TEMP, "gender");
				insertColumn(db, sql, TABLE_USERS_TEMP, "birthday");
				insertColumn(db, sql, TABLE_USERS_TEMP, "province");
				insertColumn(db, sql, TABLE_USERS_TEMP, "city");
				insertColumn(db, sql, TABLE_USERS_TEMP, "sign");
				insertColumn(db, sql, TABLE_USERS_TEMP, "wechat");
				insertColumn(db, sql, TABLE_USERS_TEMP, "qq");
				insertColumn(db, sql, TABLE_USERS_TEMP, "weibo");
				insertColumn(db, sql, TABLE_USERS_TEMP, "authData");
				insertColumn(db, sql, TABLE_USERS_TEMP, "album");
				insertColumn(db, sql, TABLE_USERS_TEMP, "backgroundImage");
				insertColumn(db, sql, TABLE_USERS_TEMP, "reserve");
				sql.append(" FROM " + TABLE_USERS_TEMP);
				db.execSQL(sql.toString());

				// 删除临时表
				db.execSQL("DROP TABLE " + TABLE_USERS_TEMP);
			}
		}

	}
	
	/**
	 * 重命名表名
	 * @param db
	 * @param tableName
	 */
	private void alterTableName(SQLiteDatabase db, String tableName) {
		if(TextUtils.isEmpty(tableName))
			return;
		
		if(!checkTableExists(db, tableName))
			return;
		
		StringBuilder sql = new StringBuilder("ALTER TABLE ");
		sql.append(tableName);
		sql.append(" RENAME TO ");
		sql.append(tableName);
		sql.append("_temp");
		
		db.execSQL(sql.toString());
	}
	
	/**
	 * 向指定表插入列
	 * @param db
	 * @param sql
	 * @param tableName 表名
	 * @param columnName 列名
	 */
	private void insertColumn(SQLiteDatabase db, StringBuilder sql, String tableName, String columnName) {
		if(checkColumnExists(db, tableName, columnName)) {
			sql.append(columnName);
		}else {
			sql.append("null");
		}
		
		if(!"reserve".equals(columnName)) {
			sql.append(",");
		}
	}
	
	/**
	 * 检查指定表是否存在
	 * @param db
	 * @param tableName 表名
	 * @return true存在  false不存在
	 */
	private boolean checkTableExists(SQLiteDatabase db, String tableName){
		String sql = "select * from sqlite_master where name=" + "'" + tableName + "'";
		Cursor cursor = db.rawQuery(sql, null);
		if(cursor.getCount() != 0){
			return true;
		}
		
		return false;
	}
	
	/**
	* 检查表中指定列是否存在
	* @param db
	* @param tableName 表名
	* @param columnName 列名
	* @return true存在  false不存在
	*/
	private boolean checkColumnExists(SQLiteDatabase db, String tableName, String columnName) {
	    boolean result = false ;
	    Cursor cursor = null ;

	    try{
	        cursor = db.rawQuery( "select * from sqlite_master where name = ? and sql like ?"
	           , new String[]{tableName , "%" + columnName + "%"} );
	        result = null != cursor && cursor.moveToFirst() ;
	    }catch (Exception e){
	    	e.printStackTrace();
	    }finally{
	        if(null != cursor && !cursor.isClosed()){
	            cursor.close() ;
	        }
	    }

	    return result ;
	}
	
	@Override
	protected void onPostMigrate() {
		// do nothing
	}
}
